﻿using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
namespace INTEX.DataBase.Tools
{
    public partial class INTEXDBC
    {
        public void UpdateMDECK(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            OracleCommand or1;
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "UPDATE MDECK SET "
            + "MDMANDANT=:MDMANDANT,"
            + "MDSA=:MDSA,"
            + "MDAUFKRS=:MDAUFKRS,"
            + "MDAUFLFD=:MDAUFLFD,"
            + "MDAUFPOS=:MDAUFPOS,"
            + "MDTYP=:MDTYP,"
            + "MDAFOLGE=:MDAFOLGE,"
            + "MDMODNR=:MDMODNR,"
            + "MDEINSTNR=:MDEINSTNR,"
            + "MDRPOS=:MDRPOS,"
            + "MDMAT=:MDMAT,"
            + "MDVAR=:MDVAR,"
            + "MDDAT=:MDDAT,"
            + "MDLO=:MDLO,"
            + "MDLB=:MDLB,"
            + "MDLP=:MDLP,"
            + "MDAUFBKZ=:MDAUFBKZ,"
            + "MDMENGE=:MDMENGE,"
            + "MDEINGET=:MDEINGET,"
            + "MDZUGET=:MDZUGET,"
            + "MDRESERV=:MDRESERV,"
            + "MDVERBR=:MDVERBR,"
            + "MDMEH=:MDMEH,"
            + "MDMATTYP=:MDMATTYP,"
            + "MDERLKZ=:MDERLKZ,"
            + "MDDISPOKZ=:MDDISPOKZ,"
            + "MDABRKZ=:MDABRKZ,"
            + "MDFILLER=:MDFILLER "
                + " WHERE  "
                + "  MDMANDANT=:MDMANDANT AND MDSA=:MDSA AND MDAUFKRS=:MDAUFKRS AND MDAUFLFD=:MDAUFLFD"
                + " AND MDAUFPOS=:MDAUFPOS AND MDTYP=:MDTYP AND MDRPOS=:MDRPOS";

            or1.Parameters.Add("MDMANDANT", OracleType.NVarChar).Value = dwData["MDMANDANT"].ToString();
            or1.Parameters.Add("MDSA", OracleType.NVarChar).Value = dwData["MDSA"].ToString();
            or1.Parameters.Add("MDAUFKRS", OracleType.NVarChar).Value = dwData["MDAUFKRS"].ToString();
            or1.Parameters.Add("MDAUFLFD", OracleType.NVarChar).Value = dwData["MDAUFLFD"].ToString();
            or1.Parameters.Add("MDAUFPOS", OracleType.NVarChar).Value = dwData["MDAUFPOS"].ToString();
            or1.Parameters.Add("MDTYP", OracleType.NVarChar).Value = dwData["MDTYP"].ToString();
            or1.Parameters.Add("MDAFOLGE", OracleType.NVarChar).Value = dwData["MDAFOLGE"].ToString();
            or1.Parameters.Add("MDMODNR", OracleType.NVarChar).Value = dwData["MDMODNR"].ToString();
            or1.Parameters.Add("MDEINSTNR", OracleType.NVarChar).Value = dwData["MDEINSTNR"].ToString();
            or1.Parameters.Add("MDRPOS", OracleType.NVarChar).Value = dwData["MDRPOS"].ToString();
            or1.Parameters.Add("MDMAT", OracleType.NVarChar).Value = dwData["MDMAT"].ToString();
            or1.Parameters.Add("MDVAR", OracleType.NVarChar).Value = dwData["MDVAR"].ToString();
            or1.Parameters.Add("MDDAT", OracleType.NVarChar).Value = dwData["MDDAT"].ToString();
            or1.Parameters.Add("MDLO", OracleType.NVarChar).Value = dwData["MDLO"].ToString();
            or1.Parameters.Add("MDLB", OracleType.NVarChar).Value = dwData["MDLB"].ToString();
            or1.Parameters.Add("MDLP", OracleType.NVarChar).Value = dwData["MDLP"].ToString();
            or1.Parameters.Add("MDAUFBKZ", OracleType.NVarChar).Value = dwData["MDAUFBKZ"].ToString();
            or1.Parameters.Add("MDMENGE", OracleType.NVarChar).Value = dwData["MDMENGE"].ToString();
            or1.Parameters.Add("MDEINGET", OracleType.NVarChar).Value = dwData["MDEINGET"].ToString();
            or1.Parameters.Add("MDZUGET", OracleType.NVarChar).Value = dwData["MDZUGET"].ToString();
            or1.Parameters.Add("MDRESERV", OracleType.NVarChar).Value = dwData["MDRESERV"].ToString();
            or1.Parameters.Add("MDVERBR", OracleType.NVarChar).Value = dwData["MDVERBR"].ToString();
            or1.Parameters.Add("MDMEH", OracleType.NVarChar).Value = dwData["MDMEH"].ToString();
            or1.Parameters.Add("MDMATTYP", OracleType.NVarChar).Value = dwData["MDMATTYP"].ToString();
            or1.Parameters.Add("MDERLKZ", OracleType.NVarChar).Value = dwData["MDERLKZ"].ToString();
            or1.Parameters.Add("MDDISPOKZ", OracleType.NVarChar).Value = dwData["MDDISPOKZ"].ToString();
            or1.Parameters.Add("MDABRKZ", OracleType.NVarChar).Value = dwData["MDABRKZ"].ToString();
            or1.Parameters.Add("MDFILLER", OracleType.NVarChar).Value = dwData["MDFILLER"].ToString();
            Connect2Proxy(or1);
        }
        public void MapMDECK(ref DataRow dwLocal, DataRow dwData)
        {
            BlindMap(ref dwLocal, dwData);
        }
        public void SaveMDECK(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            DataSet setResult = new DataSet();
            DataTable tbLocal = null;
            DataRow dwLocal = null;
            bool flgExist = false;
            setResult = QueryMDECK(dwData, 2);
            if (setResult.Tables.Count > 0)
            {
                tbLocal = setResult.Tables[0];
                if (tbLocal.Rows.Count > 0)
                {
                    flgExist = true;
                    dwLocal = tbLocal.Rows[0];
                }
                else
                {
                    dwLocal = tbLocal.NewRow();
                }
            }
            MapMDECK(ref dwLocal, dwData);

            if (!flgExist)
            {
                InsertMDECK(dwLocal);
            }
            UpdateMDECK(dwLocal);
        }
        public void InsertMDECK(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            OracleCommand or1;
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "INSERT INTO MDECK(MDMANDANT, MDSA, MDAUFKRS, MDAUFLFD, MDAUFPOS, MDTYP, MDAFOLGE, MDMODNR, MDEINSTNR, MDRPOS, MDMAT, MDVAR, MDDAT, MDLO, MDLB, MDLP, MDAUFBKZ, MDMENGE, MDEINGET, MDZUGET, MDRESERV, MDVERBR, MDMEH, MDMATTYP, MDERLKZ, MDDISPOKZ, MDABRKZ, MDFILLER)"
                + " VALUES (:MDMANDANT,:MDSA, :MDAUFKRS, :MDAUFLFD, :MDAUFPOS,:MDTYP, '010', '000', '000', :MDRPOS, '00000000111000', '001', 20130806, 'TX', 'GM', '        ', ' ', 22340000, 0, 0, 0, 0, 'PCS', ' ', ' ', 'V', 'E', '00000000                         ')";
            or1.Parameters.Add("MDMANDANT", OracleType.NVarChar).Value = dwData["MDMANDANT"].ToString();
            or1.Parameters.Add("MDSA", OracleType.NVarChar).Value = dwData["MDSA"].ToString();
            or1.Parameters.Add("MDAUFKRS", OracleType.NVarChar).Value = dwData["MDAUFKRS"].ToString();
            or1.Parameters.Add("MDAUFLFD", OracleType.NVarChar).Value = dwData["MDAUFLFD"].ToString();
            or1.Parameters.Add("MDAUFPOS", OracleType.NVarChar).Value = dwData["MDAUFPOS"].ToString();
            or1.Parameters.Add("MDTYP", OracleType.NVarChar).Value = dwData["MDTYP"].ToString();
            or1.Parameters.Add("MDRPOS", OracleType.NVarChar).Value = dwData["MDRPOS"].ToString();
            Connect2Proxy(or1);
        }
        public void DeleteMDECK(string client, string krs, string lfd, string pos, int option = 1)
        {

            OracleCommand or1 = OracleDB.CreateCommand();

            switch (option)
            { 
                case 1:
                    or1.CommandText = "DELETE FROM MDECK "
            + " WHERE MDMANDANT=:MDMANDANT  AND MDAUFKRS=:MDAUFKRS  AND MDAUFLFD=:MDAUFLFD AND MDAUFPOS=:MDAUFPOS";
                    or1.Parameters.Add("MDMANDANT", OracleType.NVarChar).Value = client;
                    or1.Parameters.Add("MDAUFKRS", OracleType.NVarChar).Value = krs;
                    or1.Parameters.Add("MDAUFLFD", OracleType.NVarChar).Value = lfd;
                    or1.Parameters.Add("MDAUFPOS", OracleType.NVarChar).Value = pos;
                    break;
                case 2:
                    or1.CommandText = "DELETE FROM MDECK "
            + " WHERE MDMANDANT=:MDMANDANT  AND MDAUFKRS=:MDAUFKRS  AND MDAUFLFD=:MDAUFLFD ";
                    or1.Parameters.Add("MDMANDANT", OracleType.NVarChar).Value = client;
                    or1.Parameters.Add("MDAUFKRS", OracleType.NVarChar).Value = krs;
                    or1.Parameters.Add("MDAUFLFD", OracleType.NVarChar).Value = lfd;

                    break;
                default:
                    break;
            }
            Connect2Proxy(or1);
        }
        public DataSet QueryMDECK(DataRow dwData, int option = 1)
        {
            DataSet Return;
            DataSet Result = new DataSet();
            OracleCommand or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 0:
                    or1.CommandText = "SELECT * FROM MDECK WHERE  ROWNUM<2 ";
                    break;
                case 1:
                    or1.CommandText = "SELECT * FROM MDECK  "
                        + " WHERE MDMANDANT=:MDMANDANT AND WDTYP='B' AND MDAUFKRS=:MDAUFKRS  AND MDAUFLFD=:MDAUFLFD AND MDAUFPOS=:MDAUFPOS";
                    or1.Parameters.Add("MDMANDANT", OracleType.NVarChar).Value = dwData["MDMANDANT"].ToString();
                    or1.Parameters.Add("MDAUFKRS", OracleType.NVarChar).Value = dwData["MDAUFKRS"].ToString();
                    or1.Parameters.Add("MDAUFLFD", OracleType.NVarChar).Value = dwData["MDAUFLFD"].ToString();
                    or1.Parameters.Add("MDAUFPOS", OracleType.NVarChar).Value = dwData["MDAUFPOS"].ToString();
                    break;
                case 2:
                    or1.CommandText = "SELECT * FROM MDECK  "
                        + " WHERE MDMANDANT=:MDMANDANT AND MDTYP=:MDTYP AND MDAUFKRS=:MDAUFKRS  AND MDAUFLFD=:MDAUFLFD AND MDAUFPOS=:MDAUFPOS AND MDRPOS=:MDRPOS";
                    or1.Parameters.Add("MDMANDANT", OracleType.NVarChar).Value = dwData["MDMANDANT"].ToString();
                    or1.Parameters.Add("MDAUFKRS", OracleType.NVarChar).Value = dwData["MDAUFKRS"].ToString();
                    or1.Parameters.Add("MDAUFLFD", OracleType.NVarChar).Value = dwData["MDAUFLFD"].ToString();
                    or1.Parameters.Add("MDAUFPOS", OracleType.NVarChar).Value = dwData["MDAUFPOS"].ToString();
                    or1.Parameters.Add("MDTYP", OracleType.NVarChar).Value = dwData["MDTYP"].ToString();
                    or1.Parameters.Add("MDRPOS", OracleType.NVarChar).Value = dwData["MDRPOS"].ToString();
                    break;
                case 3:
                    or1.CommandText = "SELECT DISTINCT ABKMANDANT, ABKAUFKRS,ABKAUFLFD,ABPART,FSABEZ,LSLIEFNA AS EXTPRODUCER,ABPLIEFNR,ABPANRKR,ABPALFDNR  "
                    + " FROM MDECK F "
                    + " JOIN ABKOPF K ON F.MDAUFKRS=K.ABKAUFKRS AND  F.MDAUFLFD=K.ABKAUFLFD"
                    + " JOIN ABPOS P ON F.MDAUFKRS=P.ABPAUFKRS AND  F.MDAUFLFD=P.ABPAUFLFD AND  F.MDAUFPOS=P.ABPAUFPOS  AND ABPGESSTA='SP'"
                    + " LEFT JOIN FSSTAMM M ON ABPART=FSART"
                        + " LEFT JOIN LIEFDAT M ON LSLIEFNR=ABPLIEFNR "
                    + " WHERE F.MDMAT=:MDMAT AND F.MDTYP=:MDTYP";
                    or1.Parameters.Add("MDMAT", OracleType.NVarChar).Value = dwData["MDMAT"].ToString();
                    or1.Parameters.Add("MDTYP", OracleType.NVarChar).Value = dwData["MDTYP"].ToString();
                    break;
                case 4:
                    or1.CommandText = "SELECT * FROM MDECK  "
                        + " WHERE MDMANDANT=:MDMANDANT AND MDTYP='B' AND MDAUFKRS=:MDAUFKRS  AND MDAUFLFD=:MDAUFLFD";
                    or1.Parameters.Add("MDMANDANT", OracleType.NVarChar).Value = dwData["MDMANDANT"].ToString();
                    or1.Parameters.Add("MDAUFKRS", OracleType.NVarChar).Value = dwData["MDAUFKRS"].ToString();
                    or1.Parameters.Add("MDAUFLFD", OracleType.NVarChar).Value = dwData["MDAUFLFD"].ToString();
                    break;
                case 8://按照原料的所有需求
                    or1.CommandText = "SELECT  DISTINCT R.* FROM INTEX.MDECK R JOIN"
                        + " INTEX.ICC_ABAUFM M  ON R.MDAUFKRS=M.ABAVKAUFKRS AND R.MDAUFLFD=M.ABAVKAUFLFD AND R.MDAUFPOS=M.ABAVKAUFPOS AND TRIM(R.MDRPOS)=TRIM(M.ABAVKUPOS) "
                        + " WHERE MDMANDANT=:MDMANDANT  AND MDTYP=:MDTYP AND MDMAT=:MDMAT AND MDVAR=:MDVAR AND MDZUGET=0 AND MDRESERV=0"
                        + " AND M.ABAAUFTYP='N' ";
                    or1.Parameters.Add("MDMANDANT", OracleType.NVarChar).Value = dwData["MDMANDANT"].ToString();
                    or1.Parameters.Add("MDTYP", OracleType.NVarChar).Value = dwData["MDTYP"].ToString();
                    or1.Parameters.Add("MDMAT", OracleType.NVarChar).Value = dwData["MDMAT"].ToString();
                    or1.Parameters.Add("MDVAR", OracleType.NVarChar).Value = dwData["MDVAR"].ToString();
                    break;
                case 9://分组
                    /*
               SELECT MDMANDANT AS MANDANT,MDTYP AS DTYP,MDMAT AS ART,MDVAR AS VAR,'          ' AS COLOR,
' ' AS CDES, '       ' AS ASIZE,
MDMEH AS UM,MAX(S.MSBEZ)AS ARTDES,SUM(ABARESMG) AS REQ,
MAX(LSLIEFNA) AS SELLER
FROM INTEX.MDECK R JOIN INTEX.ICC_ABAUFM M
ON R.MDAUFKRS=M.ABAVKAUFKRS AND R.MDAUFLFD=M.ABAVKAUFLFD
AND R.MDAUFPOS=M.ABAVKAUFPOS  AND R.MDRPOS=M.ABAVKUPOS AND TRIM(ABAART)=TRIM(MDMAT) AND TRIM(ABAVAR)=TRIM(MDVAR)
LEFT JOIN MSSTAMM S ON R.MDMAT=S.MSMAT AND R.MDVAR=S.MSVAR
LEFT JOIN MSEINK K ON R.MDMAT=K.MEMAT AND R.MDVAR=K.MEVAR
LEFT JOIN LIEFDAT ON MELIEFNR=LSLIEFNR
 WHERE MDMANDANT='00'  AND MDTYP='B'
 AND MDZUGET=0 AND MDRESERV=0 AND M.ABAAUFTYP='N'
 GROUP BY MDMANDANT,MDTYP,MDMAT,MDVAR,MDMEH
 ORDER BY MDMANDANT,MDTYP,MDMAT,MDVAR,MDMEH
                     */
                    or1.CommandText = "SELECT MDMANDANT AS MANDANT,MDTYP AS DTYP,MDMAT AS ART,MDVAR AS VAR,'          ' AS COLOR,"
                        + "' ' AS CDES, '       ' AS ASIZE,ABACOLORREF AS CREF,MDMEH AS UM,MAX(Trim(S.MSBEZ))AS ARTDES,SUM(ABARESMG) AS REQ,"
                        + "NVL(MAX(SP.LSLIEFNA),'YCA') AS SELLER,NVL(MAX(SP.LSNAME1),'YCA') AS SELLERNAME "
                        + " FROM INTEX.MDECK R JOIN INTEX.ICC_ABAUFM M"
                        + " ON R.MDAUFKRS=M.ABAVKAUFKRS AND R.MDAUFLFD=M.ABAVKAUFLFD"
                        + " AND R.MDAUFPOS=M.ABAVKAUFPOS  AND R.MDRPOS=M.ABAVKUPOS AND TRIM(ABAART)=TRIM(MDMAT) AND TRIM(ABAVAR)=TRIM(MDVAR)"
                        + " LEFT JOIN MSSTAMM S ON R.MDMAT=S.MSMAT AND R.MDVAR=S.MSVAR"
                        + " LEFT JOIN MSEINK K ON R.MDMAT=K.MEMAT AND R.MDVAR=K.MEVAR"
                        + " LEFT JOIN LIEFDAT SP ON MELIEFNR=SP.LSLIEFNR"
                        + "  WHERE  MDMANDANT=:MDMANDANT  AND MDTYP=:MDTYP"
                        + " AND MDZUGET=0 AND MDRESERV=0 AND M.ABAAUFTYP='N'"
                        + " GROUP BY MDMANDANT,MDTYP,MDMAT,MDVAR,MDMEH,ABACOLORREF"
                        + " ORDER BY MDMANDANT,MDTYP,MDMAT,MDVAR,MDMEH,ABACOLORREF";
                    or1.Parameters.Add("MDMANDANT", OracleType.NVarChar).Value = dwData["MDMANDANT"].ToString();
                    or1.Parameters.Add("MDTYP", OracleType.NVarChar).Value = dwData["MDTYP"].ToString();
                    break;
                case 99:
                    or1.CommandText = "SELECT 	MDAUFKRS,MDAUFLFD,MDAUFPOS,MDMAT,MDVAR,	MDMENGE,MDEINGET,	MDZUGET,	MDRESERV,MDVERBR,MDMEH,0 AS RATIO"
                        + " FROM MDECK  "
                        + " WHERE MDMANDANT=:MDMANDANT AND MDTYP='B' AND MDAUFKRS=:MDAUFKRS  AND MDAUFLFD=:MDAUFLFD";
                    or1.Parameters.Add("MDMANDANT", OracleType.NVarChar).Value = dwData["MDMANDANT"].ToString();
                    or1.Parameters.Add("MDAUFKRS", OracleType.NVarChar).Value = dwData["MDAUFKRS"].ToString();
                    or1.Parameters.Add("MDAUFLFD", OracleType.NVarChar).Value = dwData["MDAUFLFD"].ToString();
                    break;
                default:
                    break;
            }
            Result = Connect2Proxy(or1);
            if (Result.Tables.Count > 0)
                Result.Tables[0].TableName = "MDECK";
            Return = Result;
            return Return;
        }
    }
}
